﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using Excel;
using Excel.Core;
using Excel.Exceptions;
using System.IO;
using System.Data;
using System.Data.Linq;
using System.Diagnostics;
namespace ExactXML
{
    class Program
    {
        static ExactDataContext DS = new ExactDataContext();
        static void Main(string[] args)
        {

           // ExportXMLInvoiceDebtor("INVOICESDEBTOR.xml");
            //ExportXMLStudent("StudentNew.xml");
            ExportXMLCreditor("Creditor.xml");
           // ExportXMLEmployee("Employee.xml");
           // ExportXMLFamily("Family.xml");
        }
        private static void StartProcessImport(string filexml)
        {
            Process Proc = new Process();
            Proc.StartInfo.FileName = @"ASImport.exe";
            Proc.StartInfo.Arguments = @"-racc-new -D200 -u -~ I -URL" + Directory.GetCurrentDirectory() + "/"+ filexml + " -Tdebtors -OPT18 -Oauto";
            Proc.StartInfo.UseShellExecute = false;
            Proc.StartInfo.RedirectStandardError = true;
            Proc.StartInfo.RedirectStandardError = true;
            Proc.Start();
            Proc.WaitForExit();
            Proc.Close();
        }
        private static void ExportXMLInvoiceDebtor(string filename)
        {
            XNamespace ns = "http://www.w3.org/2001/XMLSchema-instance";

            XDocument doc = new XDocument(new XDeclaration("1.0", "utf-8", null));
            XElement elementRoot = new XElement("eExact", new XAttribute(XNamespace.Xmlns + "xsi", ns),
                new XAttribute(ns + "noNamespaceSchemaLocation", "eExact-Schema.xsd"));


            doc.Add(elementRoot);
            XElement elementAccounts = new XElement("Accounts");
            elementRoot.Add(elementAccounts);


            var query = from InvDebtor in DS.ISHCMC_All_Invoice_Debtors
                        select InvDebtor;
            foreach (ISHCMC_All_Invoice_Debtor item in query)
            {





                string DebtorID = item.NewDebtorExact.Value.ToString();
                string DebtorName = item.Debtor_Name;
                string Address1 = item.Address1;
                string Address2 = item.Address2;
                string Address3 = item.Address3;

                string DebtorCode = DebtorID.ToString();
                string AccountCode = DebtorID.ToString();
                string Phone = "";
                string Fax = "";
                string Email = "";
                string HomePage = "";

                while (DebtorCode.Length < 6)
                {
                    DebtorCode = " " + DebtorCode;
                }

                while (AccountCode.Length < 20)
                {
                    AccountCode = " " + AccountCode;
                }



                XElement elementAccount = new XElement("Account",
                                 new XAttribute("code", AccountCode),
                                 new XAttribute("status", "A"),
                                 new XAttribute("type", "C"));

                XElement elementName = new XElement("Name", DebtorName);
                elementAccount.Add(elementName);
                XElement elementPhone = new XElement("Phone", Phone);
                elementAccount.Add(elementPhone);
                XElement elementFax = new XElement("Fax", Fax);
                elementAccount.Add(elementFax);
                XElement elementEmail = new XElement("Email", Email);
                elementAccount.Add(elementEmail);
                XElement elementHomePage = new XElement("HomePage", HomePage);
                elementAccount.Add(elementHomePage);

                XElement elementContacts = new XElement("Contacts");
                elementAccount.Add(elementContacts);

                XElement elementContact = new XElement("Contact",
                    new XAttribute("default", "1"),
                    new XAttribute("gender", "O"),
                    new XAttribute("status", "A"));


                XElement elementLastName = new XElement("LastName", "Unknown");
                elementContact.Add(elementLastName);
                XElement elementFirstName = new XElement("FirstName", "Unknown");
                elementContact.Add(elementFirstName);
                XElement elementTitle = new XElement("Title",
                    new XAttribute("code", "LS"));
                elementContact.Add(elementTitle);

                XElement elementAddresses = new XElement("Addresses");
                elementContact.Add(elementAddresses);

                string[] AddressType = { "D", "I", "P", "V" };
                foreach (string itemAddressType in AddressType)
                {
                    XElement elementAddress = new XElement("Address",
                        new XAttribute("type", itemAddressType),
                        new XAttribute("desc", ""));


                    XElement elementAddressLine1 = new XElement("AddressLine1", Address1);
                    elementAddress.Add(elementAddressLine1);
                    XElement elementAddressLine2 = new XElement("AddressLine2", Address2);
                    elementAddress.Add(elementAddressLine2);
                    XElement elementAddressLine3 = new XElement("AddressLine2", Address3);
                    elementAddress.Add(elementAddressLine3);
                    XElement elementPostalCode = new XElement("PostalCode", "");
                    elementAddress.Add(elementPostalCode);
                    XElement elementCity = new XElement("City", "");
                    elementAddress.Add(elementCity);
                    XElement elementState = new XElement("State", new XAttribute("code", ""));
                    elementAddress.Add(elementState);

                    XElement elementCountry = new XElement("Country", new XAttribute("code", "VN"));
                    elementAddress.Add(elementCountry);
                    XElement elementPhoneContact = new XElement("Phone");
                    elementAddress.Add(elementPhoneContact);

                    XElement elementFaxContact = new XElement("Fax");
                    elementAddress.Add(elementPhoneContact);

                    elementAddresses.Add(elementAddress);
                }
                XElement elementLanguageCode = new XElement("Language", new XAttribute("code", "EN"));
                elementContact.Add(elementLanguageCode);
                elementContacts.Add(elementContact);

                XElement elementManager = new XElement("Manager", new XAttribute("number", "1"));
                elementAccount.Add(elementManager);

                XElement elementDebtor = new XElement("Debtor",
                    new XAttribute("number", DebtorCode),
                    new XAttribute("code", AccountCode));
                elementAccount.Add(elementDebtor);

                XElement elementCurrency = new XElement("Currency",
                    new XAttribute("code", "VND"));
                elementDebtor.Add(elementCurrency);

                XElement elementSecurity = new XElement("SecurityLevel", "10");
                elementDebtor.Add(elementSecurity);

                XElement elementVAT = new XElement("VATLiability", "L");
                elementAccount.Add(elementVAT);

                XElement elementCompanySize = new XElement("CompanySize", new XAttribute("code", "UNKNOWN"));
                elementAccount.Add(elementCompanySize);

                XElement elementCompanyOrigin = new XElement("CompanyOrgin", new XAttribute("code", "P"));
                elementAccount.Add(elementCompanyOrigin);

                XElement elementCompanyRating = new XElement("CompanyRating", new XAttribute("code", "1"));
                elementAccount.Add(elementCompanyOrigin);


                if (DebtorName.ToLower().StartsWith("mr") || DebtorName.ToLower().StartsWith("mrs") || DebtorName.ToLower().Contains("mr") || DebtorName.ToLower().Contains("mrs"))
                {
                    XElement elementAccountCategory = new XElement("AccountCategory", new XAttribute("code", "FA"));
                    elementAccount.Add(elementAccountCategory);
                }
                else
                {

                    XElement elementAccountCategory = new XElement("AccountCategory", new XAttribute("code", "CO"));
                    elementAccount.Add(elementAccountCategory);
                }

                elementAccounts.Add(elementAccount);



            }

            doc.Save(filename);

        }
        private static void ExportXMLStudent(string filename)
        {
            XNamespace ns = "http://www.w3.org/2001/XMLSchema-instance";

            XDocument doc = new XDocument(new XDeclaration("1.0", "utf-8", null));
            XElement elementRoot = new XElement("eExact", new XAttribute(XNamespace.Xmlns + "xsi", ns),
                new XAttribute(ns + "noNamespaceSchemaLocation", "eExact-Schema.xsd"));


            doc.Add(elementRoot);
            XElement elementAccounts = new XElement("Accounts");
            elementRoot.Add(elementAccounts);


            var query = from InvDebtor in DS.ISHCMC_IS_Student_Debtors
                        select InvDebtor;
            foreach (ISHCMC_IS_Student_Debtor item in query)
            {





                string DebtorID = item.NewDebtorExact.Value.ToString();
                string DebtorName = item.Student___Last_Name.ToUpper() + " " + item.Student___first_Name.ToUpper();
                string Address1 = item.Address1;
                string Address2 = item.Address2;
                string Address3 = item.Address3;

                string DebtorCode = DebtorID.ToString();
                string AccountCode = DebtorID.ToString();
                string Phone = "";
                string Fax = "";
                string Email = "";
                string HomePage = "";

                while (DebtorCode.Length < 6)
                {
                    DebtorCode = " " + DebtorCode;
                }

                while (AccountCode.Length < 20)
                {
                    AccountCode = " " + AccountCode;
                }



                XElement elementAccount = new XElement("Account",
                                 new XAttribute("code", AccountCode),
                                 new XAttribute("status", "A"),
                                 new XAttribute("type", "C"));

                XElement elementName = new XElement("Name", DebtorName);
                elementAccount.Add(elementName);
                XElement elementPhone = new XElement("Phone", Phone);
                elementAccount.Add(elementPhone);
                XElement elementFax = new XElement("Fax", Fax);
                elementAccount.Add(elementFax);
                XElement elementEmail = new XElement("Email", Email);
                elementAccount.Add(elementEmail);
                XElement elementHomePage = new XElement("HomePage", HomePage);
                elementAccount.Add(elementHomePage);

                XElement elementContacts = new XElement("Contacts");
                elementAccount.Add(elementContacts);

                XElement elementContact = new XElement("Contact",
                    new XAttribute("default", "1"),
                    new XAttribute("gender", "O"),
                    new XAttribute("status", "A"));


                XElement elementLastName = new XElement("LastName", "Unknown");
                elementContact.Add(elementLastName);
                XElement elementFirstName = new XElement("FirstName", "Unknown");
                elementContact.Add(elementFirstName);
                XElement elementTitle = new XElement("Title",
                    new XAttribute("code", "LS"));
                elementContact.Add(elementTitle);

                XElement elementAddresses = new XElement("Addresses");
                elementContact.Add(elementAddresses);

                string[] AddressType = { "D", "I", "P", "V" };
                foreach (string itemAddressType in AddressType)
                {
                    XElement elementAddress = new XElement("Address",
                        new XAttribute("type", itemAddressType),
                        new XAttribute("desc", ""));


                    XElement elementAddressLine1 = new XElement("AddressLine1", Address1);
                    elementAddress.Add(elementAddressLine1);
                    XElement elementAddressLine2 = new XElement("AddressLine2", Address2);
                    elementAddress.Add(elementAddressLine2);
                    XElement elementAddressLine3 = new XElement("AddressLine2", Address3);
                    elementAddress.Add(elementAddressLine3);
                    XElement elementPostalCode = new XElement("PostalCode", "");
                    elementAddress.Add(elementPostalCode);
                    XElement elementCity = new XElement("City", "");
                    elementAddress.Add(elementCity);
                    XElement elementState = new XElement("State", new XAttribute("code", ""));
                    elementAddress.Add(elementState);

                    XElement elementCountry = new XElement("Country", new XAttribute("code", "VN"));
                    elementAddress.Add(elementCountry);
                    XElement elementPhoneContact = new XElement("Phone");
                    elementAddress.Add(elementPhoneContact);

                    XElement elementFaxContact = new XElement("Fax");
                    elementAddress.Add(elementPhoneContact);

                    elementAddresses.Add(elementAddress);
                }
                XElement elementLanguageCode = new XElement("Language", new XAttribute("code", "EN"));
                elementContact.Add(elementLanguageCode);
                elementContacts.Add(elementContact);

                XElement elementManager = new XElement("Manager", new XAttribute("number", "1"));
                elementAccount.Add(elementManager);

                XElement elementDebtor = new XElement("Debtor",
                    new XAttribute("number", DebtorCode),
                    new XAttribute("code", AccountCode));
                elementAccount.Add(elementDebtor);

                XElement elementCurrency = new XElement("Currency",
                    new XAttribute("code", "VND"));
                elementDebtor.Add(elementCurrency);

                XElement elementSecurity = new XElement("SecurityLevel", "10");
                elementDebtor.Add(elementSecurity);

                XElement elementVAT = new XElement("VATLiability", "L");
                elementAccount.Add(elementVAT);

                XElement elementCompanySize = new XElement("CompanySize", new XAttribute("code", "UNKNOWN"));
                elementAccount.Add(elementCompanySize);

                XElement elementCompanyOrigin = new XElement("CompanyOrgin", new XAttribute("code", "P"));
                elementAccount.Add(elementCompanyOrigin);

                XElement elementCompanyRating = new XElement("CompanyRating", new XAttribute("code", "1"));
                elementAccount.Add(elementCompanyOrigin);


                
                XElement elementAccountCategory = new XElement("AccountCategory", new XAttribute("code", "ST"));
                elementAccount.Add(elementAccountCategory);
             
                elementAccounts.Add(elementAccount);



            }

            doc.Save(filename);

        }
        private static void ExportXMLCreditor(string filename)
        {
            XNamespace ns = "http://www.w3.org/2001/XMLSchema-instance";

            XDocument doc = new XDocument(new XDeclaration("1.0", "utf-8", null));
            XElement elementRoot = new XElement("eExact", new XAttribute(XNamespace.Xmlns + "xsi", ns),
                new XAttribute(ns + "noNamespaceSchemaLocation", "eExact-Schema.xsd"));


            doc.Add(elementRoot);
            XElement elementAccounts = new XElement("Accounts");
            elementRoot.Add(elementAccounts);


            var query = from Supplier in DS.ISHCMC_UploadedVendors
                        select Supplier;
            foreach (ISHCMC_UploadedVendor item in query)
            {





                string CreditorID = item.CreditorCode.Value.ToString();
                string CreditorName = item.Name;
                string Address1 = item.invoice_information;
                string Address2 = "";
                string Address3 = "";

                string CreditorCode = CreditorID.ToString();
                string AccountCode = CreditorID.ToString();
                string Phone = item.Phone;
                string Fax = "";
                string Email = "";
                string HomePage = "";

                while (CreditorCode.Length < 6)
                {
                    CreditorCode = " " + CreditorCode;
                }

                while (AccountCode.Length < 20)
                {
                    AccountCode = " " + AccountCode;
                }



                XElement elementAccount = new XElement("Account",
                                 new XAttribute("code", AccountCode),
                                 new XAttribute("status", "A"),
                                 new XAttribute("type", "S"));

                XElement elementName = new XElement("Name", CreditorName);
                elementAccount.Add(elementName);
                XElement elementPhone = new XElement("Phone", Phone);
                elementAccount.Add(elementPhone);
                XElement elementFax = new XElement("Fax", Fax);
                elementAccount.Add(elementFax);
                XElement elementEmail = new XElement("Email", Email);
                elementAccount.Add(elementEmail);
                XElement elementHomePage = new XElement("HomePage", HomePage);
                elementAccount.Add(elementHomePage);

                XElement elementContacts = new XElement("Contacts");
                elementAccount.Add(elementContacts);

                XElement elementContact = new XElement("Contact",
                    new XAttribute("default", "1"),
                    new XAttribute("gender", "O"),
                    new XAttribute("status", "A"));


                XElement elementLastName = new XElement("LastName", "Unknown");
                elementContact.Add(elementLastName);
                XElement elementFirstName = new XElement("FirstName", "Unknown");
                elementContact.Add(elementFirstName);
                XElement elementTitle = new XElement("Title",
                    new XAttribute("code", "LS"));
                elementContact.Add(elementTitle);

                XElement elementAddresses = new XElement("Addresses");
                elementContact.Add(elementAddresses);

                string[] AddressType = { "D", "I", "P", "V" };
                foreach (string itemAddressType in AddressType)
                {
                    XElement elementAddress = new XElement("Address",
                        new XAttribute("type", itemAddressType),
                        new XAttribute("desc", ""));


                    XElement elementAddressLine1 = new XElement("AddressLine1", Address1);
                    elementAddress.Add(elementAddressLine1);
                    XElement elementAddressLine2 = new XElement("AddressLine2", Address2);
                    elementAddress.Add(elementAddressLine2);
                    XElement elementAddressLine3 = new XElement("AddressLine2", Address3);
                    elementAddress.Add(elementAddressLine3);
                    XElement elementPostalCode = new XElement("PostalCode", "");
                    elementAddress.Add(elementPostalCode);
                    XElement elementCity = new XElement("City", "");
                    elementAddress.Add(elementCity);
                    XElement elementState = new XElement("State", new XAttribute("code", ""));
                    elementAddress.Add(elementState);
                  
                        XElement elementCountry = new XElement("Country", new XAttribute("code", "VN"));
                        elementAddress.Add(elementCountry);
                 
                    XElement elementPhoneContact = new XElement("Phone",Phone);
                    elementAddress.Add(elementPhoneContact);

                    XElement elementFaxContact = new XElement("Fax",Fax);
                    elementAddress.Add(elementFaxContact);

                    elementAddresses.Add(elementAddress);
                }
                XElement elementLanguageCode = new XElement("Language", new XAttribute("code", "EN"));
                elementContact.Add(elementLanguageCode);
                elementContacts.Add(elementContact);

                XElement elementManager = new XElement("Manager", new XAttribute("number", "1"));
                elementAccount.Add(elementManager);

                XElement elementDebtor = new XElement("Creditor",
                    new XAttribute("number", CreditorCode),
                    new XAttribute("code", AccountCode));
                elementAccount.Add(elementDebtor);
             
                    XElement elementCurrency = new XElement("Currency",
                        new XAttribute("code", "VND"));
                    elementDebtor.Add(elementCurrency);
        

                XElement elementSecurity = new XElement("SecurityLevel", "10");
                elementDebtor.Add(elementSecurity);

                XElement elementVAT = new XElement("VATLiability", "L");
                elementAccount.Add(elementVAT);
                XElement elementVATNumber = new XElement("VATNumber", item.VAT_number);
                elementAccount.Add(elementVATNumber);
                XElement elementCompanySize = new XElement("CompanySize", new XAttribute("code", "UNKNOWN"));
                elementAccount.Add(elementCompanySize);

                XElement elementCompanyOrigin = new XElement("CompanyOrgin", new XAttribute("code", "P"));
                elementAccount.Add(elementCompanyOrigin);

                XElement elementCompanyRating = new XElement("CompanyRating", new XAttribute("code", "1"));
                elementAccount.Add(elementCompanyOrigin);


               
                    XElement elementAccountCategory = new XElement("AccountCategory", new XAttribute("code", "VN"));
                    elementAccount.Add(elementAccountCategory);
             

                elementAccounts.Add(elementAccount);



            }

            doc.Save(filename);
        }
        private static void ExportXMLEmployee(string filename)
        {
            XNamespace ns = "http://www.w3.org/2001/XMLSchema-instance";

            XDocument doc = new XDocument(new XDeclaration("1.0", "utf-8", null));
            XElement elementRoot = new XElement("eExact", new XAttribute(XNamespace.Xmlns + "xsi", ns),
                new XAttribute(ns + "noNamespaceSchemaLocation", "eExact-Schema.xsd"));


            doc.Add(elementRoot);
            XElement elementAccounts = new XElement("Accounts");
            elementRoot.Add(elementAccounts);


            var query = from Supplier in DS.ISHCMC_UploadedEmployees
                        select Supplier;
            foreach (ISHCMC_UploadedEmployee item in query)
            {





                string CreditorID = (800000 + item.ID.Value).ToString();
                string CreditorName = item.Name;
                string Address1 = item.Address1;
                string Address2 = item.Address2;
                string Address3 = item.Address3;

                string CreditorCode = CreditorID.ToString();
                string AccountCode = CreditorID.ToString();
                string Phone = "";
                string Fax = "";
                string Email = "";
                string HomePage = "";

                while (CreditorCode.Length < 6)
                {
                    CreditorCode = " " + CreditorCode;
                }

                while (AccountCode.Length < 20)
                {
                    AccountCode = " " + AccountCode;
                }



                XElement elementAccount = new XElement("Account",
                                 new XAttribute("code", AccountCode),
                                 new XAttribute("status", "A"),
                                 new XAttribute("type", "E"));

                XElement elementName = new XElement("Name", CreditorName);
                elementAccount.Add(elementName);
                XElement elementPhone = new XElement("Phone", Phone);
                elementAccount.Add(elementPhone);
                XElement elementFax = new XElement("Fax", Fax);
                elementAccount.Add(elementFax);
                XElement elementEmail = new XElement("Email", Email);
                elementAccount.Add(elementEmail);
                XElement elementHomePage = new XElement("HomePage", HomePage);
                elementAccount.Add(elementHomePage);

                XElement elementContacts = new XElement("Contacts");
                elementAccount.Add(elementContacts);

                XElement elementContact = new XElement("Contact",
                    new XAttribute("default", "1"),
                    new XAttribute("gender", "O"),
                    new XAttribute("status", "A"));


                XElement elementLastName = new XElement("LastName", "Unknown");
                elementContact.Add(elementLastName);
                XElement elementFirstName = new XElement("FirstName", "Unknown");
                elementContact.Add(elementFirstName);
                XElement elementTitle = new XElement("Title",
                    new XAttribute("code", "LS"));
                elementContact.Add(elementTitle);

                XElement elementAddresses = new XElement("Addresses");
                elementContact.Add(elementAddresses);

                string[] AddressType = { "D", "I", "P", "V" };
                foreach (string itemAddressType in AddressType)
                {
                    XElement elementAddress = new XElement("Address",
                        new XAttribute("type", itemAddressType),
                        new XAttribute("desc", ""));


                    XElement elementAddressLine1 = new XElement("AddressLine1", Address1);
                    elementAddress.Add(elementAddressLine1);
                    XElement elementAddressLine2 = new XElement("AddressLine2", Address2);
                    elementAddress.Add(elementAddressLine2);
                    XElement elementAddressLine3 = new XElement("AddressLine2", Address3);
                    elementAddress.Add(elementAddressLine3);
                    XElement elementPostalCode = new XElement("PostalCode", "");
                    elementAddress.Add(elementPostalCode);
                    XElement elementCity = new XElement("City", "");
                    elementAddress.Add(elementCity);
                    XElement elementState = new XElement("State", new XAttribute("code", ""));
                    elementAddress.Add(elementState);
                  
                        XElement elementCountry = new XElement("Country", new XAttribute("code", "VN"));
                        elementAddress.Add(elementCountry);
                  
                    XElement elementPhoneContact = new XElement("Phone", Phone);
                    elementAddress.Add(elementPhoneContact);

                    XElement elementFaxContact = new XElement("Fax", Fax);
                    elementAddress.Add(elementFaxContact);

                    elementAddresses.Add(elementAddress);
                }
                XElement elementLanguageCode = new XElement("Language", new XAttribute("code", "EN"));
                elementContact.Add(elementLanguageCode);
                elementContacts.Add(elementContact);

                XElement elementManager = new XElement("Manager", new XAttribute("number", "1"));
                elementAccount.Add(elementManager);

                XElement elementCreditor = new XElement("Creditor",
                    new XAttribute("number", CreditorCode),
                    new XAttribute("code", AccountCode));
                elementAccount.Add(elementCreditor);
              
                    XElement elementCurrency = new XElement("Currency",
                        new XAttribute("code", "VND"));
                    elementCreditor.Add(elementCurrency);
           

                XElement elementSecurity = new XElement("SecurityLevel", "10");
                elementCreditor.Add(elementSecurity);

                XElement elementVAT = new XElement("VATLiability", "L");
                elementAccount.Add(elementVAT);
            
                XElement elementCompanySize = new XElement("CompanySize", new XAttribute("code", "UNKNOWN"));
                elementAccount.Add(elementCompanySize);

                XElement elementCompanyOrigin = new XElement("CompanyOrgin", new XAttribute("code", "P"));
                elementAccount.Add(elementCompanyOrigin);

                XElement elementCompanyRating = new XElement("CompanyRating", new XAttribute("code", "1"));
                elementAccount.Add(elementCompanyOrigin);



                XElement elementAccountCategory = new XElement("AccountCategory", new XAttribute("code", item.Classification));
                elementAccount.Add(elementAccountCategory);


                elementAccounts.Add(elementAccount);



            }

            doc.Save(filename);
        }
        private static void ExportXMLFamily(string filename)
        {
            XNamespace ns = "http://www.w3.org/2001/XMLSchema-instance";

            XDocument doc = new XDocument(new XDeclaration("1.0", "utf-8", null));
            XElement elementRoot = new XElement("eExact", new XAttribute(XNamespace.Xmlns + "xsi", ns),
                new XAttribute(ns + "noNamespaceSchemaLocation", "eExact-Schema.xsd"));


            doc.Add(elementRoot);
            XElement elementAccounts = new XElement("Accounts");
            elementRoot.Add(elementAccounts);


            var query = from InvDebtor in DS.ISHCMC_ISIS_FAMs
                        select InvDebtor;
            foreach (ISHCMC_ISIS_FAM item in query)
            {





                string DebtorID = item.NewDebtorExact.Value.ToString();
                string DebtorName = item.FamilyName;
                string Address1 = "UNKNOWN";
                string Address2 = "";
                string Address3 = "";

                string DebtorCode = DebtorID.ToString();
                string AccountCode = DebtorID.ToString();
                string Phone = "";
                string Fax = "";
                string Email = "";
                string HomePage = "";

                while (DebtorCode.Length < 6)
                {
                    DebtorCode = " " + DebtorCode;
                }

                while (AccountCode.Length < 20)
                {
                    AccountCode = " " + AccountCode;
                }



                XElement elementAccount = new XElement("Account",
                                 new XAttribute("code", AccountCode),
                                 new XAttribute("status", "A"),
                                 new XAttribute("type", "C"));

                XElement elementName = new XElement("Name", DebtorName);
                elementAccount.Add(elementName);
                XElement elementPhone = new XElement("Phone", Phone);
                elementAccount.Add(elementPhone);
                XElement elementFax = new XElement("Fax", Fax);
                elementAccount.Add(elementFax);
                XElement elementEmail = new XElement("Email", Email);
                elementAccount.Add(elementEmail);
                XElement elementHomePage = new XElement("HomePage", HomePage);
                elementAccount.Add(elementHomePage);

                XElement elementContacts = new XElement("Contacts");
                elementAccount.Add(elementContacts);

                XElement elementContact = new XElement("Contact",
                    new XAttribute("default", "1"),
                    new XAttribute("gender", "O"),
                    new XAttribute("status", "A"));


                XElement elementLastName = new XElement("LastName", "Unknown");
                elementContact.Add(elementLastName);
                XElement elementFirstName = new XElement("FirstName", "Unknown");
                elementContact.Add(elementFirstName);
                XElement elementTitle = new XElement("Title",
                    new XAttribute("code", "LS"));
                elementContact.Add(elementTitle);

                XElement elementAddresses = new XElement("Addresses");
                elementContact.Add(elementAddresses);

                string[] AddressType = { "D", "I", "P", "V" };
                foreach (string itemAddressType in AddressType)
                {
                    XElement elementAddress = new XElement("Address",
                        new XAttribute("type", itemAddressType),
                        new XAttribute("desc", ""));


                    XElement elementAddressLine1 = new XElement("AddressLine1", Address1);
                    elementAddress.Add(elementAddressLine1);
                    XElement elementAddressLine2 = new XElement("AddressLine2", Address2);
                    elementAddress.Add(elementAddressLine2);
                    XElement elementAddressLine3 = new XElement("AddressLine2", Address3);
                    elementAddress.Add(elementAddressLine3);
                    XElement elementPostalCode = new XElement("PostalCode", "");
                    elementAddress.Add(elementPostalCode);
                    XElement elementCity = new XElement("City", "");
                    elementAddress.Add(elementCity);
                    XElement elementState = new XElement("State", new XAttribute("code", ""));
                    elementAddress.Add(elementState);

                    XElement elementCountry = new XElement("Country", new XAttribute("code", "VN"));
                    elementAddress.Add(elementCountry);
                    XElement elementPhoneContact = new XElement("Phone");
                    elementAddress.Add(elementPhoneContact);

                    XElement elementFaxContact = new XElement("Fax");
                    elementAddress.Add(elementPhoneContact);

                    elementAddresses.Add(elementAddress);
                }
                XElement elementLanguageCode = new XElement("Language", new XAttribute("code", "EN"));
                elementContact.Add(elementLanguageCode);
                elementContacts.Add(elementContact);

                XElement elementManager = new XElement("Manager", new XAttribute("number", "1"));
                elementAccount.Add(elementManager);

                XElement elementDebtor = new XElement("Debtor",
                    new XAttribute("number", DebtorCode),
                    new XAttribute("code", AccountCode));
                elementAccount.Add(elementDebtor);

                XElement elementCurrency = new XElement("Currency",
                    new XAttribute("code", "VND"));
                elementDebtor.Add(elementCurrency);

                XElement elementSecurity = new XElement("SecurityLevel", "10");
                elementDebtor.Add(elementSecurity);

                XElement elementVAT = new XElement("VATLiability", "L");
                elementAccount.Add(elementVAT);

                XElement elementCompanySize = new XElement("CompanySize", new XAttribute("code", "UNKNOWN"));
                elementAccount.Add(elementCompanySize);

                XElement elementCompanyOrigin = new XElement("CompanyOrgin", new XAttribute("code", "P"));
                elementAccount.Add(elementCompanyOrigin);

                XElement elementCompanyRating = new XElement("CompanyRating", new XAttribute("code", "1"));
                elementAccount.Add(elementCompanyOrigin);


               

                    XElement elementAccountCategory = new XElement("AccountCategory", new XAttribute("code", "FA"));
                    elementAccount.Add(elementAccountCategory);
               

                elementAccounts.Add(elementAccount);



            }

            doc.Save(filename);

        }

    }
}
